VERSION 5.00
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "msflxgrd.ocx"
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCTL.OCX"
Begin VB.Form frmROE 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "ROE report"
   ClientHeight    =   6765
   ClientLeft      =   45
   ClientTop       =   435
   ClientWidth     =   9270
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   ScaleHeight     =   6765
   ScaleWidth      =   9270
   Begin VB.TextBox txtROEDate 
      Height          =   495
      Left            =   4320
      TabIndex        =   20
      Top             =   1200
      Visible         =   0   'False
      Width           =   1455
   End
   Begin VB.TextBox txtOffice 
      Height          =   495
      Left            =   5280
      TabIndex        =   19
      Top             =   1320
      Visible         =   0   'False
      Width           =   1455
   End
   Begin MSFlexGridLib.MSFlexGrid Grid1 
      Height          =   4455
      Left            =   0
      TabIndex        =   0
      Top             =   1920
      Width           =   9135
      _ExtentX        =   16113
      _ExtentY        =   7858
      _Version        =   393216
      SelectionMode   =   1
   End
   Begin VB.CommandButton cmdCancel 
      Caption         =   "Cancel"
      Enabled         =   0   'False
      Height          =   375
      Left            =   2880
      TabIndex        =   16
      Top             =   1200
      Width           =   1095
   End
   Begin VB.TextBox txtid 
      Height          =   285
      Left            =   4320
      TabIndex        =   15
      Top             =   2400
      Visible         =   0   'False
      Width           =   495
   End
   Begin MSComctlLib.StatusBar StatusBar1 
      Align           =   2  'Align Bottom
      Height          =   375
      Left            =   0
      TabIndex        =   14
      Top             =   6390
      Width           =   9270
      _ExtentX        =   16351
      _ExtentY        =   661
      _Version        =   393216
      BeginProperty Panels {8E3867A5-8586-11D1-B16A-00C0F0283628} 
         NumPanels       =   1
         BeginProperty Panel1 {8E3867AB-8586-11D1-B16A-00C0F0283628} 
         EndProperty
      EndProperty
   End
   Begin VB.CommandButton Command3 
      Caption         =   "Close"
      Height          =   375
      Left            =   8160
      TabIndex        =   13
      Top             =   1320
      Width           =   975
   End
   Begin VB.Frame Frame1 
      Caption         =   "Filter"
      Height          =   1095
      Left            =   3000
      TabIndex        =   7
      Top             =   0
      Width           =   4935
      Begin VB.ComboBox cboOff 
         Height          =   315
         ItemData        =   "frmROE.frx":0000
         Left            =   720
         List            =   "frmROE.frx":0016
         TabIndex        =   17
         Text            =   "ALL"
         Top             =   600
         Width           =   1695
      End
      Begin VB.ComboBox cboMonthS 
         Height          =   315
         Left            =   720
         TabIndex        =   10
         Top             =   240
         Width           =   1095
      End
      Begin VB.ComboBox cboYearS 
         Height          =   315
         Left            =   2400
         TabIndex        =   9
         Top             =   240
         Width           =   1095
      End
      Begin VB.CommandButton cmdSearch 
         Caption         =   "Search"
         Height          =   375
         Left            =   3120
         TabIndex        =   8
         Top             =   600
         Width           =   1575
      End
      Begin VB.Label Label10 
         AutoSize        =   -1  'True
         Caption         =   "Office"
         Height          =   195
         Left            =   120
         TabIndex        =   18
         Top             =   600
         Width           =   420
      End
      Begin VB.Label Label5 
         Caption         =   "Month"
         Height          =   255
         Left            =   120
         TabIndex        =   12
         Top             =   240
         Width           =   735
      End
      Begin VB.Label Label6 
         Caption         =   "Year"
         Height          =   255
         Left            =   1920
         TabIndex        =   11
         Top             =   240
         Width           =   735
      End
   End
   Begin VB.CommandButton cmdDel 
      Caption         =   "Del"
      Height          =   375
      Left            =   1800
      TabIndex        =   6
      Top             =   1200
      Width           =   975
   End
   Begin VB.CommandButton cmdAdd 
      Caption         =   "Add"
      Height          =   375
      Left            =   480
      TabIndex        =   5
      Top             =   1200
      Width           =   1215
   End
   Begin MSComCtl2.DTPicker DTPicker1 
      Height          =   375
      Left            =   720
      TabIndex        =   2
      Top             =   240
      Width           =   1815
      _ExtentX        =   3201
      _ExtentY        =   661
      _Version        =   393216
      Format          =   21168129
      CurrentDate     =   39642
   End
   Begin VB.TextBox txtROE 
      Height          =   375
      Left            =   720
      TabIndex        =   1
      Top             =   720
      Width           =   1815
   End
   Begin VB.Label Label2 
      Caption         =   "ROE"
      Height          =   255
      Left            =   240
      TabIndex        =   4
      Top             =   720
      Width           =   375
   End
   Begin VB.Label Label1 
      AutoSize        =   -1  'True
      Caption         =   "Date"
      Height          =   195
      Left            =   240
      TabIndex        =   3
      Top             =   240
      Width           =   345
   End
End
Attribute VB_Name = "frmROE"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub cmdAdd_Click()
Dim strDay, strMonth, strYear As String
If Len(Trim(txtROE.Text)) < 1 Then
    MsgBox "The ROE box must be filled in", vbInformation + vbOKOnly, "Message"
    Exit Sub
End If

If Len(DTPicker1.Month) < 2 Then
    strMonth = "0" & DTPicker1.Month
Else
    strMonth = DTPicker1.Month
End If

If Len(DTPicker1.Day) < 2 Then
    strDay = "0" & DTPicker1.Day
Else
    strDay = DTPicker1.Day
End If
    If cmdAdd.Caption = "Save change" Then
        sql = "update roe set dateROE ='" & DTPicker1.Year & strMonth & strDay & "', roe='" & txtROE.Text & "' where id=" & txtID.Text
        cmdAdd.Caption = "Add"
        cmdCancel.Enabled = False
        adoConnection.Execute (sql)
        txtROE.Text = ""
        'txtid.Text = ""
    Else
    
        If addROE(DTPicker1.Year & strMonth & strDay, txtROE.Text, CurrentUser) Then
            MsgBox "Add ROE successfully", vbInformation + vbOKOnly, "ROE massage"
        Else
            MsgBox "Add ROE unsuccessfull", vbInformation + vbOKOnly, "ROE massage"
        End If
    End If
ReceiverData
End Sub

Private Sub cmdCancel_Click()
txtID.Text = ""
cmdAdd.Caption = "Add"
cmdCancel.Enabled = False
txtROE.Text = ""
End Sub

Private Sub cmdDel_Click()
If Len(Trim(txtID.Text)) > 0 Then
    If MsgBox("Are you sure to delete this row?", vbQuestion + vbYesNo, "Delete confirm") = vbYes Then
        If fcnRemoveRecordset(txtROEDate.Text, txtOffice.Text) Then
            MsgBox "Delete successfully", vbInformation + vbOKOnly, "Delete message"
            ReceiverData
            txtID.Text = ""
        Else
            MsgBox "Delete unsuccessful, Please contact with mr Tung at Hanoi Office!", vbCritical + vbOKOnly, "Delete message"
        End If
    Else
    
    End If
Else
    MsgBox "You must be select at least one row to delete!", vbInformation + vbOKOnly, "Delete balance"
End If

End Sub
Private Function fcnRemoveRecordset(dateROE As String, Office As String) As Boolean
Dim sql As String
On Error GoTo L
fcnRemoveRecordset = True
sql = "Delete * from ROE where office='" & Office & "' and dateROE ='" & dateROE & "'"
adoConnection.Execute sql
Exit Function
L:
fcnRemoveRecordset = False
End Function
Private Sub cmdSearch_Click()
ReceiverData
End Sub

Private Sub Command1_Click()
'MsgBox getAverageROE(cboMonthS.Text, cboYearS.Text)
End Sub

Private Sub Command3_Click()
Unload Me
End Sub

Private Sub ReceiverData()
    Dim i As Integer
    Dim sql As String
    Dim rs As New ADODB.Recordset
    
    If permit <> "A" Then
        If cboMonthS.Text = "ALL" Then
            sql = "Select * from roe where office ='" & stroffice & "' and left(dateROE,4) ='" & cboYearS.Text & " '"
        Else
            sql = "Select * from roe where office ='" & stroffice & "' and left(dateROE,4) ='" & cboYearS.Text & " ' and mid(dateROE,5,2)='" & cboMonthS.Text & "'"
        End If
    Else
        If UCase(cboOff.Text) = "ALL" Then
            If cboMonthS.Text = "ALL" Then
                sql = "Select * from roe where  office like '%' and left(dateROE,4) ='" & cboYearS.Text & " '"
            Else
                sql = "Select * from roe where  office like '%' and left(dateROE,4) ='" & cboYearS.Text & " ' and mid(dateROE,5,2)='" & cboMonthS.Text & "'"
            End If
        Else
            If cboMonthS.Text = "ALL" Then
                sql = "Select * from roe where     office like '" & cboOff.Text & "' and left(dateROE,4) ='" & cboYearS.Text & " '"
            Else
                sql = "Select * from roe where    office like '" & cboOff.Text & "' and left(dateROE,4) ='" & cboYearS.Text & " ' and mid(dateROE,5,2)='" & cboMonthS.Text & "'"
            End If
        End If
    End If
    rs.Open sql, adoConnection, adOpenDynamic, adLockOptimistic
    If rs.RecordCount > 0 Then
        Call Format
        Grid1.rows = rs.RecordCount
    Else
        Grid1.rows = 1
        Exit Sub
    End If
    i = 1
    Grid1.AddItem ""
    With rs
        If (Not (.BOF And .EOF)) Then
         Do Until rs.EOF
                
                Grid1.TextMatrix(i, 0) = i
                Grid1.TextMatrix(i, 1) = Right(.Fields("dateROE").Value, 2) & UCase(Left(MonthName(Mid(.Fields("dateROE").Value, 5, 2)), 3)) & Left(.Fields("dateROE").Value, 4)
                Grid1.TextMatrix(i, 2) = .Fields("roe").Value
                Grid1.TextMatrix(i, 4) = .Fields("LOGINID").Value
                Grid1.TextMatrix(i, 3) = .Fields("id").Value
                Grid1.TextMatrix(i, 5) = .Fields("OFFICE").Value
            i = i + 1
            N = i
        .MoveNext
        Loop
        .MoveFirst
        End If
    End With
    rs.Close
    Set rs = Nothing
End Sub
Public Sub Format()
    Grid1.Clear
    Grid1.rows = 2
    Grid1.cols = 6
    Grid1.TextMatrix(0, 0) = "ORDINAL"
    Grid1.TextMatrix(0, 1) = "ROE DATE"
    Grid1.TextMatrix(0, 2) = "ROE"
    Grid1.TextMatrix(0, 4) = "LOGIN ID"
    Grid1.TextMatrix(0, 5) = "OFFICE"
    Grid1.ColWidth(0) = 1000
    Grid1.ColWidth(1) = 1500
    Grid1.ColWidth(2) = 1500
    Grid1.ColWidth(3) = 0
    Grid1.ColWidth(4) = 1500
    Grid1.ColWidth(5) = 1500
End Sub

Private Sub Form_Load()
Me.Top = Screen.Height / 2 - (Me.Height / 1.5)
    Me.Left = Screen.Width / 2 - (Me.Width / 2)
Call KetNoi
    DTPicker1.Value = Now()
    DTPicker1.Format = dtpCustom
    DTPicker1.CustomFormat = "dd/MMM/yyyy"
    cboMonthS.AddItem "ALL"
    'cboYearS.AddItem "ALL"
    For i = 1 To 9
        cboMonthS.AddItem "0" & i
        If Val(i) = Month(Now()) Then
            cboMonthS.ListIndex = i
        End If
    Next
    For i = 10 To 12
        cboMonthS.AddItem i
    Next
    For i = 2008 To Year(Now()) + 1
        cboYearS.AddItem i
    Next
    cboYearS.Text = Year(Now())
    cboMonthS.Text = "ALL"
    Format
    ReceiverData
    If permit <> "A" Then
        cboOff.Enabled = False
    Else
        cboOff.Enabled = True
    End If
End Sub

Private Function addROE(roeDate As String, ROE As Variant, loginID As String) As Boolean
On Error GoTo L
Dim sql As String
Dim rs As New ADODB.Recordset
sql = "Select * from roe where office ='" & stroffice & "' and dateroe ='" & roeDate & "'"
Set rs = adoConnection.Execute(sql)
If Not rs.EOF Then
    If MsgBox("ROE of this date is already in database, do you want to overwite this one?", vbQuestion + vbYesNo, "Add ROE question") = vbYes Then
        sql = "delete * from roe where office ='" & stroffice & "' and dateroe ='" & roeDate & "'"
        adoConnection.Execute (sql)
    Else
        addROE = False
        Exit Function
    End If
End If
sql = "insert into roe(dateROE,roe,loginID,office) values(" & roeDate & "," & ROE & ",'" & loginID & "','" & stroffice & "')"
adoConnection.Execute sql
addROE = True
Exit Function
L:
addROE = False

End Function

Private Sub Grid1_Click()
If (Grid1.rows > 1) Then
    DTPicker1.Day = 1 'temp
    DTPicker1.Month = Month("01/" & Mid(Grid1.TextMatrix(Grid1.Row, 1), 3, 3) & "/2008")
    DTPicker1.Day = Left(Grid1.TextMatrix(Grid1.Row, 1), 2)
    DTPicker1.Year = Right(Grid1.TextMatrix(Grid1.Row, 1), 4)
    txtROE.Text = Grid1.TextMatrix(Grid1.Row, 2)
    txtID.Text = Grid1.TextMatrix(Grid1.Row, 3)
    txtOffice.Text = Grid1.TextMatrix(Grid1.Row, 5)
    txtROEDate.Text = formatYYYYMMDD(Left(Grid1.TextMatrix(Grid1.Row, 1), 2), Month("01/" & Mid(Grid1.TextMatrix(Grid1.Row, 1), 3, 3) & "/2008"), Right(Grid1.TextMatrix(Grid1.Row, 1), 4))
    cmdAdd.Caption = "Save change"
    cmdCancel.Enabled = True
End If
End Sub
